/*
 * Based on the article from 
 * http://www.reigndesign.com/blog/using-your-own-sqlite-database-in-android-applications/
 */

package d512a.TAKO.helpers;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;

import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteConstraintException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteException;
import android.database.sqlite.SQLiteOpenHelper;

public class DataBaseHelper extends SQLiteOpenHelper {

    // The Android's default system path of your application database.
    private static String DB_PATH = "/data/data/d512a.TAKO/databases/";
    private static String DB_NAME = "foodDBFinal.db3";
    private static final int DATABASE_VERSION = 1;

    private SQLiteDatabase myDataBase;
    

    private final Context myContext;

    /**
     * Constructor takes and keeps a reference of the passed context in order to
     * access the application assets and resources.
     * 
     * @param context
     */
    public DataBaseHelper(Context context) {
        super(context, DB_NAME, null, DATABASE_VERSION);
        this.myContext = context;
    }

    /**
     * Creates an empty database on the system and rewrites it with your own
     * database.
     * */
    public void createDataBase() throws IOException {
        if (checkDataBase()) {
            // do nothing - database already exist
        } else {

            // By calling this method and empty database will be created into
            // the default system path
            // of your application so we are gonna be able to overwrite that
            // database with our database.
            this.getReadableDatabase().close();

            try {
                copyDataBase();
            } catch (IOException e) {
                throw e;
            }
        }
    }

    /**
     * Checks if the database already exist to avoid re-copying the file each
     * time you open the application.
     * 
     * @return true if it exists, false if it doesn't
     **/
    private boolean checkDataBase() {
        SQLiteDatabase checkDB = null;

        try {
            String myPath = DB_PATH + DB_NAME;
            checkDB = SQLiteDatabase.openDatabase(myPath, null,
                            SQLiteDatabase.NO_LOCALIZED_COLLATORS);

        } catch (SQLiteException e) {
            // database does't exist yet.
        }

        if (checkDB != null) {
            checkDB.close();
        }

        return checkDB != null ? true : false;
    }

    /**
     * Copies your database from your local assets-folder to the just created
     * empty database in the system folder, from where it can be accessed and
     * handled. This is done by transferring bytestream.
     **/
    private void copyDataBase() throws IOException {
        File fileTest = myContext.getFileStreamPath(DB_NAME);
        boolean exists = fileTest.exists();
        if (!exists) {
            // Open the empty db as the output stream
            OutputStream databaseOutputStream = new FileOutputStream(DB_PATH + DB_NAME);
            InputStream databaseInputStream;

            databaseInputStream = myContext.getAssets().open("foodDBFinalaa");
            byte[] buffer = new byte[1024];
            // int length;
            while ((databaseInputStream.read(buffer)) > 0) {
                databaseOutputStream.write(buffer);
            }
            databaseInputStream.close();

            databaseInputStream = myContext.getAssets().open("foodDBFinalab");
            while ((databaseInputStream.read(buffer)) > 0) {
                databaseOutputStream.write(buffer);
            }
            databaseInputStream.close();

            databaseInputStream = myContext.getAssets().open("foodDBFinalac");
            while ((databaseInputStream.read(buffer)) > 0) {
                databaseOutputStream.write(buffer);
            }
            // Close the streams
            databaseInputStream.close();
            databaseOutputStream.flush();
            databaseOutputStream.close();
        }
    }

    public void openDataBase() throws SQLException {
        // Open the database
        String myPath = DB_PATH + DB_NAME;

        myDataBase = SQLiteDatabase.openDatabase(myPath, null,
                        SQLiteDatabase.NO_LOCALIZED_COLLATORS);
    }

    @Override
    public synchronized void close() {

        if (myDataBase != null)
            myDataBase.close();

        super.close();
    }

    @Override
    public void onCreate(SQLiteDatabase db) {

    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

    }

    // Add your public helper methods to access and get content from the
    // database.
    // You could return cursors by doing "return myDataBase.query(....)" so it'd
    // be easy to you to create adapters for your views.

    public Cursor searchProducts(String productName) {
        // Perform a managed query. The Activity will handle closing
        // and re-querying the cursor when needed.
        return this.getReadableDatabase().rawQuery(
                        "SELECT _id, DanName FROM FoodInfo2 WHERE DanName LIKE ?",
                        new String[] { "%" + productName.trim() + "%" });
    }

    /**
     * Get the product's name from a barcode
     */
    public Cursor getProductNameFromFoodId(String foodId) {
        return this.getReadableDatabase().rawQuery(
                        "SELECT DanName FROM FoodInfo2 WHERE FoodId = ?", new String[] { foodId });
    }

    /**
     * Get the product's name from a barcode
     */
    public Cursor getProductNameFromBarcode(long barcode) {
        return this.getReadableDatabase().rawQuery(
                        "SELECT DanName FROM FoodInfo2 WHERE FoodInfo2._id = "
                                        + "(SELECT ProductName FROM Products WHERE Barcode = ?)",
                        new String[] { Long.toString(barcode) });

    }

    /**
     * Get basic nutrients for a product from its foodId
     */
    // public Cursor getBasicNutrientsFromFoodId(String foodId) {
    // return this.getReadableDatabase().rawQuery(
    // "SELECT CompId, BestLoc FROM Nutrients2 WHERE FoodId = ? " +
    // "AND CompId IN ('0001', '0003', '0004', '0005', '0006', '0008', '0009')"
    // +
    // "ORDER BY CompId", new String[] { foodId });
    // }

    public Cursor getKiloJoule(String foodId) {
        return this.getReadableDatabase().rawQuery(
                        "SELECT CompId , BestLoc FROM Nutrients2 WHERE FoodId = ? "
                                        + "AND CompId IN ('0000') ", new String[] { foodId });
    }

    /**
     * Get basic nutrients for a product from its barcode
     */
    public Cursor getNutrientsFromFoodIdForRating(String date) {
        return this
                        .getReadableDatabase()
                        .rawQuery(
                                        "SELECT CompId, BestLoc "
                                                        + "FROM Nutrients2 "
                                                        + "WHERE FoodId IN ("
                                                        + "SELECT FoodId "
                                                        + "FROM FoodInfo2, Basket "
                                                        + "WHERE FoodInfo2._id == Basket.ProductName "
                                                        + "AND Basket.Dato = ?) "
                                                        + "AND CompId IN ('0001', '0003', '0004', '0005', '0006', '0008', '0009', '0011', '0012', '0013')"
                                                        + "ORDER BY CompId",
                                        new String[] { date.trim() });
    }

    /**
     * Get basic nutrients for a product from its barcode
     */
    public Cursor getBasicNutrientsFromBarcode(long barcode) {
        return this
                        .getReadableDatabase()
                        .rawQuery(
                                        "SELECT * FROM Nutrients2 "
                                                        + "JOIN CompName2 ON Nutrients2.CompId = CompName2.CompId "
                                                        + "WHERE FoodId = "
                                                        + "(SELECT FoodId FROM FoodInfo2 WHERE _id = "
                                                        + "(SELECT ProductName FROM Products WHERE barcode = ?)) "
                                                        + "AND CompName2.CompId IN ('0000', '0001', '0003', '0008')"
                                                        + "ORDER BY SrtOrd",
                                        new String[] { Long.toString(barcode) });

    }

    /**
     * Get vitamin nutrients for a product
     */
    public Cursor getVitaminsNutrientsFromFoodId(String foodId) {
        return this
                        .getReadableDatabase()
                        .rawQuery(
                                        "SELECT CompId, BestLoc "
                                                        + "FROM Nutrients2 "
                                                        + "WHERE FoodId = ? "
                                                        + "AND CompId IN ('0014','0023','0026','0035','0036','0037','0038','0041','0045','0046','0047','0049','0050')"
                                                        + "ORDER BY CompId",
                                        new String[] { foodId });
    }

    /**
     * Get mineral nutrients for a product
     */
    public Cursor getMineralsNutrientsFromFoodId(String foodId) {
        return this
                        .getReadableDatabase()
                        .rawQuery(
                                        "SELECT CompId, BestLoc "
                                                        + "FROM Nutrients2 "
                                                        + "WHERE FoodId = ? "
                                                        + "AND CompId IN ('0056','0057','0058','0059','0060','0061','0064','0065','0066','0067','0068','0069','0072')"
                                                        + "ORDER BY CompId",
                                        new String[] { foodId });
    }

    /**
     * Get more detailed nutrients for a product
     */
    public Cursor getDetailedNutrientsFromFoodId(String foodId) {
        return this.getReadableDatabase().rawQuery(
                        "SELECT CompId, BestLoc " + "FROM Nutrients2 " + "WHERE FoodId = ? "
                                        + "AND CompId < '0014' " + "ORDER BY CompId",
                        new String[] { foodId });
    }

    /**
     * Get more detailed nutrients for a product
     */
    public Cursor getDetailedNutrientsFromBarcode(long barcode) {
        return this
                        .getReadableDatabase()
                        .rawQuery(
                                        "SELECT * FROM Nutrients2 "
                                                        + "JOIN CompName2 ON Nutrients2.CompId = CompName2.CompId "
                                                        + "WHERE FoodId = "
                                                        + "(SELECT FoodId FROM FoodInfo2 WHERE _id = "
                                                        + "(SELECT ProductName FROM Products WHERE barcode = ?)) ORDER BY SrtOrd",
                                        new String[] { Long.toString(barcode) });
    }

    /**
     * Get id of a product
     */
    public Cursor getProductId(String productName) {
        return this.getReadableDatabase().rawQuery("SELECT _id FROM FoodInfo2 WHERE DanName == ?",
                        new String[] { productName.trim() });
    }

    public Cursor getProductName_id(String foodId) {
        return this.getReadableDatabase().rawQuery("SELECT _id FROM FoodInfo2 WHERE FoodId == ?",
                        new String[] { foodId.trim() });
    }

    public Cursor getRecommendedPrimKey_id() {
        return this.getReadableDatabase().rawQuery("SELECT max(_id) FROM Recommended",
                        new String[] {});
    }

    /**
     * Get items in the basket
     */
    public Cursor getItemsInBasket(String date) {
        return this.getReadableDatabase().rawQuery(
                        "SELECT DanName, ProductName, FoodId " + "FROM FoodInfo2, Basket "
                                        + "WHERE FoodInfo2._id == Basket.ProductName "
                                        + "AND Basket.Dato = ?", new String[] { date.trim() });
    }

    public Cursor getItemsInBasket() {
        return this
                        .getReadableDatabase()
                        .rawQuery(
                                        "SELECT Dato FROM FoodInfo2, Basket WHERE FoodInfo2._id == Basket.ProductName ORDER BY Dato DESC",
                                        new String[] {});
    }

    /**
     * Put an item into the basket
     */
    public void putItemInBasket(String barcode, String date) {
        try {
            this
                            .getReadableDatabase()
                            .execSQL(
                                            "INSERT INTO Basket (ProductName, Dato) "
                                                            + "VALUES ((SELECT ProductName FROM Products WHERE Barcode = ?), ?)",
                                            new String[] { barcode.trim(), date.trim() });
        } catch (SQLiteConstraintException e) {
            throw e;
        }
    }

    /**
     * Put an item into the basket
     */
    public void putItemInBasket(String barcode, String date, int quantity) {
        try {
            this
                            .getReadableDatabase()
                            .execSQL(
                                            "INSERT INTO Basket (ProductName, Dato, Quantity) VALUES ((SELECT ProductName FROM Products WHERE Barcode = ?), ?, ?)",
                                            new String[] { barcode.trim(), date.trim(),
                                                            Integer.toString(quantity) });
        } catch (SQLiteConstraintException e) {
            throw e;
        }
    }

    /**
     * Remove an item from the basket
     */
    public void removeItemFromBasket(String productName) {
        this.getReadableDatabase().execSQL("DELETE FROM Basket WHERE _id = (SELECT max(_id) FROM Basket where ProductName = ?)",
                        new String[] { productName.trim() });
    }

    // public Cursor getAllRowsFromFoodInfo2() {
    // return this.getReadableDatabase().rawQuery("SELECT * FROM FoodInfo2", new
    // String[] {});
    // }
    //	
    // public Cursor getMejeriProdukter() {
    // return this.getReadableDatabase().rawQuery(
    // "SELECT FoodId " +
    // "FROM FoodInfo2 " +
    // "WHERE MainGrp = '01' ",
    // new String[] { });
    // }
    // public Cursor getOst() {
    // return this.getReadableDatabase().rawQuery(
    // "SELECT FoodId " +
    // "FROM FoodInfo2 " +
    // "WHERE MainGrp = '02' ",
    // new String[] { });
    // }
    // public Cursor getKoedFjearAeg() {
    // return this.getReadableDatabase().rawQuery(
    // "SELECT FoodId " +
    // "FROM FoodInfo2 " +
    // "WHERE MainGrp IN ('07', '09', '10') ",
    // new String[] { });
    // }
    // public Cursor getFisk() {
    // return this.getReadableDatabase().rawQuery(
    // "SELECT FoodId " +
    // "FROM FoodInfo2 " +
    // "WHERE MainGrp = '08' ",
    // new String[] { });
    // }
    // public Cursor getBagvaerk() {
    // return this.getReadableDatabase().rawQuery(
    // "SELECT FoodId " +
    // "FROM FoodInfo2 " +
    // "WHERE MainGrp = '04' " +
    // "AND SubGrp IN ('00','10','40','50', '51','52','53','54','55', '70') ",
    // new String[] { });
    // }
    // public Cursor getMelGryn() {
    // return this.getReadableDatabase().rawQuery(
    // "SELECT FoodId " +
    // "FROM FoodInfo2 " +
    // "WHERE MainGrp = '04' " +
    // "AND SubGrp IN ('20','30') ",
    // new String[] { });
    // }
    // public Cursor getMorgenmadsprodukter() {
    // return this.getReadableDatabase().rawQuery(
    // "SELECT FoodId " +
    // "FROM FoodInfo2 " +
    // "WHERE MainGrp = '04' " +
    // "AND SubGrp = '60' ",
    // new String[] { });
    // }
    // public Cursor getFrugtGront() {
    // return this.getReadableDatabase().rawQuery(
    // "SELECT FoodId " +
    // "FROM FoodInfo2 " +
    // "WHERE MainGrp = '05' " +
    // "OR (MainGrp = '06' AND SubGrp NOT IN ('80')) ",
    // new String[] { });
    // }
    // public Cursor getFaerdigRetter() {
    // return this.getReadableDatabase().rawQuery(
    // "SELECT FoodId " +
    // "FROM FoodInfo2 " +
    // "WHERE MainGrp = '15' " +
    // "AND SubGrp NOT IN ('50','80') ",
    // new String[] { });
    // }
    // public Cursor getFedtstoffer() {
    // return this.getReadableDatabase().rawQuery(
    // "SELECT FoodId " +
    // "FROM FoodInfo2 " +
    // "WHERE MainGrp = '11' " +
    // "OR (MainGrp = '14' AND SubGrp = '80' ) " +
    // "OR (MainGrp = '15' AND SubGrp = '80' ) ",
    // new String[] { });
    // }
    // public Cursor getDrikkevarer() {
    // return this.getReadableDatabase().rawQuery(
    // "SELECT FoodId " +
    // "FROM FoodInfo2 " +
    // "WHERE MainGrp = '06' " +
    // "AND SubGrp = '80' " +
    // "OR (MainGrp = '13' AND SubGrp NOT IN ('11', '12'))",
    // new String[] { });
    // }
    // public Cursor getDesserter() {
    // return this.getReadableDatabase().rawQuery(
    // "SELECT FoodId " +
    // "FROM FoodInfo2 " +
    // "WHERE MainGrp = MainGrp = '15' " +
    // "AND SubGrp = '50' " +
    // "OR (MainGrp IN ('03', '12', '17'))",
    // new String[] { });
    // }
    //		
    // /**
    // * Get values used for rating a food item
    // */
    // public Cursor getValuesForRating(String foodId) {
    // return this.getReadableDatabase().rawQuery(
    // "SELECT CompId, BestLoc " +
    // "FROM Nutrients2 " +
    // "WHERE FoodId = ? " +
    // "AND CompId IN ( '0003', '0004', '0138', '0010', '0056') ",
    // new String[] { foodId });
    // }
    // public Cursor getValuesForRatingFrugtGront(String foodId) {
    // return this.getReadableDatabase().rawQuery(
    // "SELECT CompId, BestLoc " +
    // "FROM Nutrients2 " +
    // "WHERE FoodId = ? " +
    // "AND CompId IN ( '0003', '0004', '0009', '0010', '0056') ",
    // new String[] { foodId });
    // }
    // public Cursor getValuesForRatingDrikkevarer(String foodId) {
    // return this.getReadableDatabase().rawQuery(
    // "SELECT CompId, BestLoc " +
    // "FROM Nutrients2 " +
    // "WHERE FoodId = ? " +
    // "AND CompId IN ( '0003', '0004', '0138', '0011', '0056') ",
    // new String[] { foodId });
    // }
    // public void updateRatingValue(String foodId, int rating) {
    // try {
    // this.getReadableDatabase().execSQL(
    // "UPDATE FoodInfo2 " + "SET Rating = ? " + "WHERE FoodId = ? ",
    // new String[] { Integer.toString(rating), foodId.trim() });
    // } catch (SQLiteConstraintException e) {
    // throw e;
    // }
    // }

    /**
     * Get rating value for a product from its foodId
     */
    public Cursor getRatingFromFoodId(String foodId) {
        return this.getReadableDatabase().rawQuery(
                        "SELECT Rating FROM FoodInfo2 " + "WHERE FoodId = ? ",
                        new String[] { foodId.trim() });
    }

    public Cursor getRecommendedProductsInCategory2(String foodId) {
        return this
                        .getReadableDatabase()
                        .rawQuery(
                                        "SELECT FoodId, DanName, Rating "
                                                        + "FROM FoodInfo2 "
                                                        + "WHERE MainGrp = (SELECT MainGrp FROM FoodInfo2 WHERE FoodId = ? ) "
                                                        + "AND SubGrp = (SELECT SubGrp FROM FoodInfo2 WHERE FoodId = ? ) "
                                                        + "AND Rating < (SELECT Rating FROM FoodInfo2 WHERE FoodId = ?) "
                                                        + "ORDER BY Rating",
                                        new String[] { foodId.trim(), foodId.trim(), foodId.trim() });
    }

    public Cursor getRecommendedProductsInCategory3(String foodId) {
        return this
                        .getReadableDatabase()
                        .rawQuery(
                                        "SELECT FoodId, DanName, Rating "
                                                        + "FROM FoodInfo2 "
                                                        + "WHERE MainGrp = (SELECT MainGrp FROM FoodInfo2 WHERE FoodId = ? ) "
                                                        + "AND SubGrp = (SELECT SubGrp FROM FoodInfo2 WHERE FoodId = ? ) "
                                                        + "AND Rating <= (SELECT Rating FROM FoodInfo2 WHERE FoodId = ?) "
                                                        + "ORDER BY Rating",
                                        new String[] { foodId.trim(), foodId.trim(), foodId.trim() });
    }

    /*
     * Brug den kun i CameraActivity
     */
    public boolean barcodeExists(String barcode, String shortbarcode) {
        Cursor cursor = this
                        .getReadableDatabase()
                        .rawQuery(
                                        "SELECT FoodId FROM FoodInfo2 WHERE FoodInfo2._id = "
                                                        + "(SELECT ProductName FROM Products WHERE Barcode = ? OR Barcode = ?)",
                                        new String[] { barcode.trim(), shortbarcode.trim() });
        if (cursor.getCount() == 1)
            return true;
        return false;
    }

    public boolean barcodeExists(String barcode) {
        Cursor cursor = this.getReadableDatabase().rawQuery(
                        "SELECT FoodId FROM FoodInfo2 WHERE FoodInfo2._id = "
                                        + "(SELECT ProductName FROM Products WHERE Barcode = ?)",
                        new String[] { barcode.trim() });
        if (cursor.getCount() == 1)
            return true;
        return false;
    }

    public Cursor getFoodId(String barcode, String shortBarcode) {
        return this
                        .getReadableDatabase()
                        .rawQuery(
                                        "SELECT FoodId FROM FoodInfo2 WHERE _id = "
                                                        + "(SELECT ProductName FROM Products WHERE Barcode = ? OR Barcode = ? )",
                                        new String[] { barcode.trim(), shortBarcode.trim() });
    }

    public void insertInBasket(int primKeyId, String quantity, String date, String rec_id,
                    String scan_id) {
        try {
            this.getReadableDatabase().execSQL(
                            "INSERT INTO Basket (ProductName, Quantity, Dato, Rec_id, Scanning_id) "
                                            + "VALUES (?, ?, ?, ?, ?)",
                            new String[] { Integer.toString(primKeyId), quantity.trim(),
                                            date.trim(), rec_id, scan_id });
        } catch (SQLiteConstraintException e) {
            throw e;
        }
    }
    
    public void insertBarcodeInProducts(String Barcode, String id) {
        try {
            this.getReadableDatabase().execSQL(
                            "INSERT INTO Products (Barcode, ProductName) "
                                            + "VALUES (?, ?)",
                            new String[] { Barcode.trim(), id.trim() });
        } catch (SQLiteConstraintException e) {
            throw e;
        }
    }

    public Cursor insertInRecommended(int primKeyId, String rec_id, String scan_id,
                    int suggestionType, String date) {
        try {
            this.getReadableDatabase().execSQL(
                            "INSERT INTO Recommended (ProductName, Rec_id, Scanning_id, SuggestionType, Date) "
                                            + "VALUES (?, ?, ?, ?, ?)",
                            new String[] { Integer.toString(primKeyId), rec_id, scan_id,
                                            Integer.toString(suggestionType), date.trim() });
            return this.getReadableDatabase().rawQuery("SELECT max(_id) FROM Recommended",
                            new String[] {});
        } catch (SQLiteConstraintException e) {
            throw e;
        }
    }

    public Cursor getBasketTable() {
        return this.getReadableDatabase().rawQuery("SELECT * FROM Basket", new String[] {});
    }

    public Cursor getRecommendedTable() {
        return this.getReadableDatabase().rawQuery("SELECT * FROM Recommended", new String[] {});
    }

    public Cursor putScannings(String barcode, String date) {
        this.getReadableDatabase().execSQL(
                        "INSERT INTO Scannings (Barcode, Date) " + "VALUES (?, ?)",
                        new String[] { barcode.trim(), date.trim() });
        return this.getReadableDatabase().rawQuery("SELECT max(_id) FROM Scannings",
                        new String[] {});
    }

    // public Cursor getLatestScanning() {
    // return
    // this.getReadableDatabase().rawQuery("SELECT max(_id) FROM Scannings", new
    // String[] {});
    // }

    public Cursor getBasket() {
        return this.getReadableDatabase().rawQuery("SELECT * FROM Basket WHERE Sent = 0",
                        new String[] {});
    }

    public Cursor getScannings() {
        return this.getReadableDatabase().rawQuery("SELECT * FROM Scannings WHERE Sent = 0",
                        new String[] {});
    }

    public Cursor getRecommended() {
        return this.getReadableDatabase().rawQuery("SELECT * FROM Recommended WHERE Sent = 0",
                        new String[] {});
    }

    public void setSentBasket() {
        this.getReadableDatabase().execSQL("UPDATE Basket SET Sent = 1", new String[] {});
    }

    public void setSentScannings() {
        this.getReadableDatabase().execSQL("UPDATE Scannings SET Sent = 1", new String[] {});
    }

    public void setSentRecommended() {
        this.getReadableDatabase().execSQL("UPDATE Recommended SET Sent = 1", new String[] {});
    }
}